package com.whale.cloud.util.rest.sql;

/**
 * Created by paco on 2017/06/07.
 */
public class SqlDefine {
		//获取配置信息
	/*GetSysConfigure*/
		//根据表ID查询表信息
		public static final String GetSysConfigure_getFunctionById_sql =
						"select st.id,st.def_corp_id,st.createid,st.createdate,st.modifyid,st.modifydate,st.is_ac,st.sort_no,st.is_show,st.img_default,st.img_hover,st.show_name,st.function_name,st.operate_rule,st.sys_function_id,st.pk_id,st.dk_id,st.ak_id,st.is_large,st.is_bc,st.bc_classname,st.is_ac,st.ac_classname,st.is_bm,st.bm_classname,st.is_am,st.am_classname,st.is_bd,st.bd_classname,st.is_submit,st.submit_classname,st.is_unsubmit,st.unsummit_classname,st.data_from,st.propertys,st.filter,st.sys_module_id,st.sys_function_category_id,st.sys_function_fold_id,st.is_cache from sys_function st where st.id=?";

		//根据表名（数据库名称）查询表信息
		public static final String GetSysConfigure_getFunctionByDBName_sql =
						"select st.id,st.def_corp_id,st.createid,st.createdate,st.modifyid,st.modifydate,st.is_ac,st.sort_no,st.is_show,st.img_default,st.img_hover,st.show_name,st.function_name,st.operate_rule,st.sys_function_id,st.pk_id,st.dk_id,st.ak_id,st.is_large,st.is_bc,st.bc_classname,st.is_ac,st.ac_classname,st.is_bm,st.bm_classname,st.is_am,st.am_classname,st.is_bd,st.bd_classname,st.is_submit,st.submit_classname,st.is_unsubmit,st.unsummit_classname,st.data_from,st.propertys,st.filter,st.sys_module_id,st.sys_function_category_id,st.sys_function_fold_id,st.is_cache from sys_function st where st.function_name=?";

		//查询用户具有的操作权限字段
		public static final String GetSysConfigure_getUserColumnById_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.max_length,c.by_name,c.sort_no,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.is_extend,c.platform_id,c.platform_type,c.data_from,ca.rwrule,ca.rwrule_terminal,c.position,c.group_name from def_function_authority dma join def_column_authority ca on ca.def_function_authority_id=dma.id join sys_column c on c.id=ca.sys_column_id where dma.sys_function_id=? and ca.is_active='Y' and (exists (select du.id from def_user du join def_role dr on du.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)') where du.id=? and dma.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)')) or dma.def_user_id=?) and ca.rwrule like ? order by ca.sort_no asc";
		//"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.interpreter,c.is_extend,c.platform_id,c.platform_type,ca.rwrule,ca.rwrule_terminal from  def_column_authority ca join sys_column c on c.id=ca.sys_column_id where ca.def_function_authority_id in(select ma.id from def_function_authority ma where ma.sys_function_id=?) and ca.rwrule like ? order by ca.sort_no asc";
		//select count(1) from sys_function_authority dma where dma.sys_function_id=2 and (exists (select du.id from def_user du join def_role dr on du.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)') where du.id=2 and dma.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)')) or dma.def_user_id=2)

		//根据字段ID查询字段信息
		public static final String GetSysConfigure_getColumnById_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.max_length from sys_column c where c.id=?";

		//根据字段ID查询扩展字段
		//public static final String GetSysConfigure_getExtendColumnById_sql="select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,'N' as \"is_pk\",'N' as \"is_dk\",'N' as \"is_ak\",c.is_nullable,c.interpreter,c.def_platform_id,c.platform_type,'Y' as \"is_extend\" from sys_extend_column c where c.id=?";

		//根据字段名（数据库名称）查询字段信息
		public static final String GetSysConfigure_getColumnByDBName_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.max_length from sys_column c where c.sys_function_id=? and c.field_name=?";

		//根据表ID，查询表下的所有字段
		public static final String GetSysConfigure_getColumnByFunctionId_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.max_length from sys_column c where c.sys_function_id=?";

		//根据表名称（数据库名称）查询表下的所有字段
		public static final String GetSysConfigure_getColumnByFunctionDBName_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.max_length from sys_column c where c.sys_function_id=(select t.id from sys_function t where t.function_name=?)";

		//根据表ID与读写规则查询表下的字段
		public static final String GetSysConfigure_getColumnByFunctionID_rwrule_sql =
						"select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.by_name,c.sort_no,c.rwrule,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.max_length from sys_column c where c.sys_function_id=? and c.rwrule like ?";

		//查询用户是否有相关菜单的操作权限
		public static final String GetSysConfigure_getUserFunctionCount_sql =
						"select count(1) from def_function_authority dma join sys_action da on dma.sys_action_ids regexp concat('(^|[:space:]+|[[:<:]])',da.id,'([[:>:]]|[:space:]+|$)') where dma.sys_function_id=? and (exists (select du.id from def_user du join def_role dr on du.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)') where du.id=? and dma.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)')) or dma.def_user_id=?) and da.code=?";
		//select count(1) from sys_function_authority dma where dma.sys_function_id=? and (exists (select du.id from def_user du join def_role dr on du.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)') where du.id=? and dma.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)')) or dma.def_user_id=?)

		public static final String GetSysConfigure_getOptionItmesById_sql =
						"select oi.id,oi.code,oi.name from sys_option_item oi where oi.sys_option_id=? order by oi.sort_no asc";

		public static final String GetSysConfigure_getUserSearchColumns_sql = "select c.id,c.def_corp_id,c.createid,c.createdate,c.modifyid,c.modifydate,c.is_active,c.show_name,c.field_name,c.max_length,c.by_name,c.sort_no,c.voluation_type,c.show_type,c.statistics_type,c.cascade_type,c.sys_function_id,c.foreign_table_id,c.sys_sequence_id,c.sys_option_id,c.default_value,c.filter,c.is_showremark,c.remark,c.is_search,c.is_canorder,c.propertys,c.is_pk,c.is_dk,c.is_ak,c.is_nullable,c.is_cache,c.interpreter,c.is_extend,c.platform_id,c.platform_type,c.data_from,ca.rwrule,ca.rwrule_terminal from def_function_authority dma join def_column_authority ca on ca.def_function_authority_id=dma.id join sys_column c on c.id=ca.sys_column_id where dma.sys_function_id=? and ca.is_active='Y' and (exists (select du.id from def_user du join def_role dr on du.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)') where du.id=? and dma.def_role_ids regexp concat('(^|[:space:]+|[[:<:]])',dr.id,'([[:>:]]|[:space:]+|$)')) or dma.def_user_id=?) and c.is_search='Y' order by ca.sort_no asc";
}
